<?php
require('fpdf.php');
include('conn.php');

	
class PDF extends FPDF
{
// Load data
// function LoadData($file)
// {
    // // Read file lines
    // $lines = file($file);
    // $data = array();
    // foreach($lines as $line)
        // $data[] = explode(';',trim($line));
    // return $data;
// }

function LoadTransactions($custid)
{

$sqlb=" select  DATE_FORMAT((select if(max(paydate) is not null,max(paydate),current_date()) from transactionmast where custid = PARAM_CUST_ID),'%d-%m-%Y') as trndate, 'Previous Balance' as remarks, b.bdesc nob, IF( (sum(t.debitamt) - sum(t.creditamt)) is NULL,0,(sum(t.debitamt) - sum(t.creditamt)) ) as debitamt, 0 as creditamt 
from customermast c 
left join transactionmast t on c.custid = t.custid 
left join businessmast b on b.bid = t.bid
where t.status = 0  
and t.custid = PARAM_CUST_ID
and t.trndate <= (select if(max(paydate) is not null,max(paydate),current_date()) from transactionmast where custid = PARAM_CUST_ID)
group by c.custid 




union all
select   DATE_FORMAT(t.trndate,'%d-%m-%Y'), t.remarks, b.bdesc nob,t.debitamt,t.creditamt
from transactionmast t 
left join businessmast b on b.bid = t.bid
where 
custid = PARAM_CUST_ID and 
t.status = 0 and t.trndate > (select if(max(paydate) is not null,max(paydate),current_date()) from transactionmast where custid = PARAM_CUST_ID)
union all


select  '' as trndate, 'Outstanding' as remarkds, ''  nob, IF( (sum(t.debitamt) - sum(t.creditamt)) is NULL,0,(sum(t.debitamt) - sum(t.creditamt)) ) as debitamt,0 as creditamt
from customermast c 
left join transactionmast t on c.custid = t.custid 
where t.status = 0  
and t.custid = PARAM_CUST_ID
group by c.custid 

";

$sqlb=str_replace('PARAM_CUST_ID',$custid,$sqlb);
//$sqlb=str_replace('PARAM_START_DATE',$startdate,$sqlb);

	$data = array();			
	$resb=mysql_query($sqlb);
	while($rowb=mysql_fetch_assoc($resb))
	{
		$rows = $rowb['trndate'].'#SEP#'.$rowb['remarks'].'#SEP#'.$rowb['nob'].'#SEP#'.$rowb['debitamt'].'#SEP#'.$rowb['creditamt'];
					$data[] = explode('#SEP#',trim($rows));
	}

			return $data;
}


// Colored table
function FancyTable($header, $rows,$custdesc,$custid)
{
		//$this->SetAutoPageBreak(true);
		
		// Customer Name: 
		$this->TableHeader($custdesc,$custid,$header);
		
		$col_widths = array(27, 85, 40, 20, 20);
        $row_line_heights = array(6, 6, 6, 6, 6); // FPDF needs the developer to set the line height for each row				
		//$this->Ln();
		$maxRowHeight = 0;
		//$this->SetMargins(3,3,0);
 
        for ($r = 0; $r < count($rows); $r++) {

		// // page break;
		//if( ($r > 0) &&  (($r % 15)==0))
		 $y = $this->GetY();
		 if($y>220)
		 {
			 $this->AddPage();
			 $this->TableHeader($custdesc,$custid,$header);
		 }
			
            $row = $rows[$r];
            $x = $this->GetX();

			$maxRowHeight = 0;
            for ($col = 0; $col < count($col_widths); $col++) {
				if($maxRowHeight < 6)
				$maxRowHeight=6;
                $yBeforeCell = $this->GetY();
                $borders = 'LB' . ($col + 1 == count($col_widths) ? 'R' : ''); // Only add R for last col
				if(($col == 3) || ($col == 4))
					$align='R';
				else
					$align='L';					
				
				if($r==count($rows)-1)
				{
					$this->SetFont('Arial', 'B', 12);
					$this->Line($this->GetX(), $this->GetY(), 210-10, $this->GetY());
				}
				$this->MultiCell($col_widths[$col], $maxRowHeight, $row[$col], 1,$align);
				$yCurrent = $this->GetY();
                $rowHeight = $yCurrent - $yBeforeCell;
				if($maxRowHeight < $rowHeight) 
					$maxRowHeight = $rowHeight;	
				 $this->SetXY($x + $col_widths[$col], $yCurrent - $rowHeight);
                 $x = $this->GetX();				
            }
			
			$this->Ln($maxRowHeight); // Line-feed at last cell height to start a new row
		}
		
		$this->Ln(10);
}

function TableHeader($custdesc,$custid, $header)
{
	$col_widths = array(27, 85, 40, 20, 20);
	$this->SetFont('Arial','',12);
	$this->Cell(0,5,'Member Name: '.$custdesc,0,0,'L');
	$this->Cell(0,5,'Date: '.date("d-m-Y"),0,1,'R');
	$this->Cell(0,5,'Member ID: '.$custid,0,1,'L');
	$this->Ln(2);
	$this->Line($this->GetX(), $this->GetY(), 210-10, $this->GetY());
 	

        // ----------- Header row -------------
		$this->SetTextColor(0,0,0);
		$this->SetFont('Arial', 'B', 10);
		for($i = 0; $i < count($header); $i++) {
		    if($i>2) 
			$this->Cell($col_widths[$i], 5, $header[$i], 1, 0, 'R');
			else
			$this->Cell($col_widths[$i], 5, $header[$i], 1, 0, 'L');
		}
		$this->Ln();
		$this->SetFont('Arial', '', 10);
		$this->SetTextColor(0,0,0);
		//----------- Header row -------------
}

// Page header
function Header()
{
    // Logo
    $this->SetFont('Arial','B',12);
    // Title
    $this->Cell(0,5,'SHREE COMMUNICATIONS',0,1,'C');
	// Company Name
	$this->SetFont('Arial','B',12);
    //Setting the text colorto black
	$this->SetTextColor(0,0,0);
	$this->Cell(0,5,'Ledger',0,1,'C');
	$this->Ln(2);
    $this->Line($this->GetX(), $this->GetY(), 210-10, $this->GetY());
	$this->Ln(2);
	//$this->Line(30, 30, 210-20, 45);
}

// Page footer
function Footer()
{
    // Position at 1.5 cm from bottom
    $this->SetY(-62);
	$this->SetFont('Arial','B',8);
	$this->Cell(0,5,'Thanking You',0,1,'L');
	$this->Cell(0,5,'Best Regards,',0,1,'L');
	$this->SetTextColor(0,0,255);
	$this->Cell(0,5,'Manish Talwar.',0,1,'L');
	$this->SetFont('Arial','I',8);
	$this->SetTextColor(0,0,0);
	$this->Cell(0, 5, 'For Shree Communications, 2, Ganesh Darshan, LBS Marg, Mulund Check Naka, Mulund W, Mumbai, 400080.', 0,1,'L');
	$this->Cell(0, 5, 'Mobile No. 02221641631 / Fax No: 02221649516 / 9819336100 / 9022300077 / 8097920321 / 9821479777 / 9323749444', 0,1,'L');
	$this->Cell(0, 5, 'Email: shreeholidays2012@yahoo.com; shreecommuni@yahoo.co.in', 0,1,'L');
	$this->Cell(0, 5, 'Bank Details:', 0,1,'L');
	$this->Cell(0, 5, 'Axis Bank:    A/c 508010300000037    IFSC Code: UTIB0000508         Branch: LBS Marg Mulund', 0,1,'L');
	$this->Cell(0, 5, 'HDFC Bank: A/c   15768620000029    IFSC Code: HDFC0001576       Branch: LBS Marg  Mulund', 0,1,'L');
	$this->Cell(0, 5, 'ICICI Bank:   A/c  124405000182         IFSC Code: ICIC0001244      Branch: Vikas Paradise Mulund', 0,1,'L');
    // Arial italic 8
    $this->SetFont('Arial','I',8);
	// add footer address
	$this->Cell(0,5,'',0,0,'L');
    // Page number
    $this->Cell(0,5,'Page '.$this->PageNo(),0,0,'R');
}

} // end class
 


$pdf = new PDF();
// Column headings
$header = array('Tran Date','Description','Business', 'Debit','Credit');
// Data loading
//$data = $pdf->LoadData('countries.txt');
$custid=$_REQUEST['custid'];
$custdesc=$_REQUEST['custdesc'];
//$startdate=$_REQUEST['startdate'];
$data = $pdf->LoadTransactions($custid);

$pdf->SetFont('Arial','',12);
// $pdf->AddPage();
// $pdf->BasicTable($header,$data);
// $pdf->AddPage();
// $pdf->ImprovedTable($header,$data);
$pdf->AddPage('P');
$pdf->FancyTable($header,$data,$custdesc,$custid);
$filename='outstanding.pdf';
$pdf->Output($filename,"D");
?>

